﻿using System;
using System.Collections.Generic;
using System.Text;
using Model = SQLServerHealthCheck.Model;
using SQLServerHealthCheck.Model;
using System.Linq;
using System.Data.SqlClient;
using SMO = Microsoft.SqlServer.Management.Smo;
using WMI = Microsoft.SqlServer.Management.Smo.Wmi;
using System.Windows.Forms;
using System.Collections;
using System.IO;

namespace SQLServerHealthCheck.SQLServer
{
   public static  class SQLAgentCheck
    {
       public static List<Exception> errors;
       public static ArrayList arrSQLAgentCheck = new ArrayList();

       public static List<Exception> GenerateSQLAgentJobsOverview(List<String> SQLServerInstances)
       {
           try
           {

               errors = new List<Exception>();

               HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"].ToString());

               dc.ClearAll();

               List<Server> servers = new List<Server>();

               //delete the Installation Check Log file if it exists
               //string InstallationCheckLogfile = Application.StartupPath + "\\SQLServerHealthCheck_InstallationCheckLog.txt";

               //if (File.Exists(InstallationCheckLogfile))
               //{
               //    File.Delete(InstallationCheckLogfile);
               //}


               ////delete the SQLAgentCheck  Log file if it exists
               //string SQLAgentCheckLogFile = Application.StartupPath + "\\SQLServerHealthCheck_SQLAgentCheckLog.txt";

               //if (File.Exists(SQLAgentCheckLogFile))
               //{
               //    File.Delete(SQLAgentCheckLogFile);
               //}


               InstallationCheck.arrInstallationCheck.Clear();
               arrSQLAgentCheck.Clear();

               InstallationCheck.FillServersAndSQLServerServices(SQLServerInstances, dc, servers);
               InstallationCheck.FillServices(dc, servers);
               SQLAgentCheck.FillSQLAgentData(SQLServerInstances, dc, servers);
           }
           catch (System.Security.SecurityException ex1)
           {
               MessageBox.Show("You don't have permission to delete  file in path " + Application.StartupPath, "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
           }

           return errors;

       }


       private static void FillSQLAgentData(List<String> SQLServerInstances, HealthCheckDataContext dc, List<Server> servers)
       {
           
           foreach (Server server in servers)
           {

               foreach (Service service in server.Services)
               {
                   if (service.ServiceTypeID == (int)WMI.ManagedServiceType.SqlServer)
                   {
                       FillSQLJobDetails(dc, service);
                      
                   }
               }

             
           }

       }


       private static void FillSQLJobDetails(HealthCheckDataContext dc, Service service)
       {

           try
           {
               //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillSQLJobDetails: ", " FillSQLJobDetails method in SQLAgentCheck Started for " + service.InstanceName.ToString());

               //Check the connection to SQL Server Instance and gets the details.
               if (InstallationCheck.IsSQLServerAccessible(@"Server=" + service.InstanceName.ToString() + ";database=master;Integrated Security=True"))
               {

                   using (SqlConnection cn = new SqlConnection(@"Server=" + service.InstanceName.ToString() + ";Database=master;Integrated Security=True"))
                   {
                       cn.Open();

                       string sqlOLE1 = "sp_configure 'Agent XPs',1 ;reconfigure;";
                       SqlCommand OLEcmd1 = new SqlCommand(sqlOLE1, cn);
                       OLEcmd1.ExecuteNonQuery();


                       String sql = @"	

CREATE TABLE #Temp1
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner   sysname
DECLARE @is_sysadmin   INT
SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner   = suser_sname ()
INSERT INTO #Temp1
 
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE #Temp1
SET last_run_time    = right ('000000' + last_run_time, 6),
next_run_time    = right ('000000' + next_run_time, 6);







SELECT j.name AS JobName,
case j.enabled
when 1 then 'Enabled'
else 'Disabled'
end
 AS Enabled,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunStatus,
c.name as Category,
CAST(
convert(datetime,cast(h.run_date as varchar))
+(cast(h.run_time as float)*(.36)+cast(h.run_time%10000 as float)*(.6-.36)+cast(h.run_time%100 as float)*(1-.6)+ run_duration)/86400

as varchar) as LastRunDate,

CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS [Duration(ms)]


FROM         #Temp1 x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON     x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0

drop table #Temp1
";



                       SqlCommand cmd = new SqlCommand(sql, cn);
                       SqlDataReader rd = cmd.ExecuteReader();


                       while (rd.Read())
                       {
                           SQLAgentJobDetails ajd = null;


                           if (service.SQLAgentJobDetails.Count(d => d.JobName == rd[1].ToString()) == 0)
                           {
                               ajd = new SQLAgentJobDetails();

                               ajd.JobName = rd[0].ToString();
                               ajd.Enabled = rd[1].ToString();
                               ajd.LastRunStatus = rd[2].ToString();
                               ajd.Category = rd[3].ToString();
                               ajd.LastRunDateTime = rd[4].ToString();
                               ajd.Duration = rd.IsDBNull(5) ? (int?)null : Convert.ToInt32(rd[5].ToString());

                               service.SQLAgentJobDetails.Add(ajd);
                           }
                           else
                           {
                               ajd = service.SQLAgentJobDetails.First(d => d.JobName == rd[1].ToString());
                           }
                       }


                       rd.Close();


                   }


                   dc.SubmitChanges();
                  // InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillSQLJobDetails: ", " FillSQLJobDetails method in SQLAgentCheck Completed for " + service.InstanceName.ToString());

               }

               else
               {
                   //Add the name of SQL Intances for which connections were not sucessfull and details were not imported

                   arrSQLAgentCheck.Add(service.InstanceName.ToString());
                   //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillDatabaseTableDetails: ", " There were problems in importing PerformanceCheck details for " + service.InstanceName.ToString() + ".Please check the connectivity to this instance");
               }

           }


           catch (Exception ex)
           {


               errors.Add(ex);
               //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillSQLJobDetails: ", " Some error occured in SQLAgentCheck.Please check exception file for details");

 
           }



       }






    }
}
